//
//  EiaCrudeOilFuturePriceCollectro.m
//  MarketDataColloctor
//
//  Created by jiro on 10/10/20.
//  Copyright 2010 jiro music. All rights reserved.
//

#import "EiaCrudeOilFuturePriceCollector.h"
#import "WebResourceCollector.h"
#import "../../../Commons/DbOperator.h"
#import "../../../DbTypes/DbUtility.h"
#import "../../../DbTypes/DbConstants.h"
#import "eiaHtmlParser.h"


@implementation EiaCrudeOilFuturePriceCollector

+ (BOOL) collect
{
	//	---- get html
	NSError* error = nil;
	id collector = [ [ WebResourceCollector alloc ] initWithEncoding: NSUTF8StringEncoding ];
	id html = nil;
//	id html = [ NSString stringWithContentsOfFile: @"a_1.html" encoding: NSUTF8StringEncoding error: & error ];
	if ( html == nil ) {
		id url = [ NSURL URLWithString: @"http://www.eia.gov/dnav/pet/pet_pri_fut_s1_d.htm" ];
		id urls = [ NSArray arrayWithObject: url ];
		id htmls = [ NSMutableArray array ];
		[ collector collectContentsOf: urls intoStrings: htmls ];
		html = [ htmls objectAtIndex: 0 ];
		[ html writeToFile: @"a_1.html" atomically: YES encoding: NSUTF8StringEncoding error: & error ];
	}
//	NSLog( @"%@", html );
	//	----

	//	---- parse html, get country, type, link url
	NSXMLDocument* document = [ [ NSXMLDocument alloc ] initWithXMLString: html options:NSXMLDocumentTidyHTML error: & error ];
	if ( document == nil ) {
		NSLog( @"xml parse error: %@", document, error );
		return NO;
	}
	
	id element = [ document rootElement ];
	id nodes = [ element nodesForXPath: @"/html/body/form/table[@class=\"data1\"]/tr[@class=\"DataRow\"]"  error: & error ];	
//	NSLog( @"%d", [ nodes count ] );
//	for ( int i = 0; i < [ nodes count ]; i++ ) {
//		NSLog( @"%@", [ nodes objectAtIndex: i ] );
//	}
	for ( int i = 1; i < 5; i++ ) {
		id node = [ nodes objectAtIndex: i ];
//		NSLog( @"%@", node );
		
		//	---- get html link and contract name
		id processingNodes = [ node nodesForXPath: @"td[@class=\"DataStub\"]/table/tr/td[@class=\"DataStub1\"]" error: & error ];
		id thisNode = [ processingNodes objectAtIndex: 0 ];
		id contractName = [ thisNode stringValue ];
		contractName = [ contractName stringByTrimmingCharactersInSet: [ NSCharacterSet whitespaceAndNewlineCharacterSet ] ];
		NSLog( @"%@", thisNode );

		processingNodes = [ node nodesForXPath: @"td[@class=\"DataHist\"]/a" error: & error ];
		thisNode = [ processingNodes objectAtIndex: 0 ];
		thisNode = [ thisNode attributeForName: @"href" ];
		id urlString = [ thisNode stringValue ];
		urlString = [ urlString stringByTrimmingCharactersInSet: [ NSCharacterSet whitespaceAndNewlineCharacterSet ] ];
		id url = [ NSURL URLWithString: [ NSString stringWithFormat: 
			@"http://www.eia.gov/dnav/pet/%@",
			[ urlString stringByReplacingOccurrencesOfString: @"./" withString: @"" ] ] ];
//		NSLog( @"%@, %@", contractName, url );
		//	----

		//	---- get contract type
		int crudeType;
		id dbOperator = [ DbOperator defaultOperator ];
		id sql = [ NSString stringWithFormat:
			@"select code from %@ where %@ = 'NYMEX Future %@';",
			kText_CrudeOilCodeTable, kText_Name, contractName ];
		PGresult* res;
		if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
			NSLog( @"fail to get crude type, message: %@, sql: %@ ", [ dbOperator error ], sql );
			continue;
		}
		crudeType = [ DbUtility parseAsDouble: PQgetvalue( res, 0, 0 ) ];
		PQclear( res );
		//	----

		//	---- get latest date for country and crude type
		int countryCode = 1; //	United States
		sql = [ NSString stringWithFormat: @"select %@ from %@ where %@ = %d and %@ = %d order by %@ desc limit 1;",
			kText_ExchangeDate, kText_CrudeOilExchangeTable,
			kText_CrudeOilProductCountryCode, countryCode, kText_CrudeOilCode, crudeType,
			kText_ExchangeDate ];
		if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
			NSLog( @"sql fail, message: %@, sql: %@", [ dbOperator errorMessage ], sql );
			continue;
		}
//		NSLog( @"%@", sql );
		NSDate* latestDate = nil;
		if ( PQnfields( res ) != 0 && PQntuples( res ) != 0 ) {
			latestDate = [ DbUtility parseAsNSDate: PQgetvalue( res, 0, 0 ) ];
		}
		PQclear( res );
		//	----

		//	---- get price data 
		id html = [ collector collectContentsOf: url ];
//		[ html writeToFile: @"a.html" atomically: YES encoding: NSUTF8StringEncoding error: & error  ];
//		id html = [ NSString stringWithContentsOfFile: @"a.html" encoding: NSUTF8StringEncoding  error: & error ];
		id results = [ NSMutableDictionary dictionary ];
		[ EiaHtmlParser parseDataHtml: html dataInterval: Daily latestDate: latestDate result: results ];
		//	----

		//	---- create sql and execute
		id sqls = [ NSMutableArray array ];
		int dataCount = 0;
		sql = [ NSString stringWithFormat: @"insert into %@ values\n", kText_CrudeOilExchangeTable ];
		NSEnumerator* e = [ results keyEnumerator ];
		NSDate* date;
		BOOL valueAdded = NO;
		while ( date = [ e nextObject ] ) {
			//	---- check the former condition
			if ( latestDate != nil && [ date timeIntervalSinceDate: latestDate ] <= 0 ) {
				continue;
			}
			id value = [ results objectForKey: date ];
			if ( [ value isEqualToString: @"NA" ] ) {
				continue;
			}
			//	----

			if ( valueAdded ) {
				sql = [ sql stringByAppendingString: @", " ];
			} else {
				valueAdded = YES;
			}
			sql = [ sql stringByAppendingFormat: @"( %d, %d, '%@', %@ )\n", 
				countryCode, crudeType, date, value ];
			dataCount++;
			
			if ( dataCount == 1000 ) {
				NSLog( @"data count: 1000" );
				sql = [ sql stringByAppendingString: @";" ];
				[ sqls addObject: sql ];
				sql = [ NSString stringWithFormat: @"insert into %@ values\n", kText_CrudeOilExchangeTable ];
				valueAdded = NO;
				dataCount = 0;
			}

		}
		if ( valueAdded ) {
			sql = [ sql stringByAppendingString: @";" ];
			[ sqls addObject: sql ];
		}
//		NSLog( @"%@", sql );
		e = [ sqls objectEnumerator ];
		while ( sql = [ e nextObject ] ) {
			if ( DbResponse_Success != [ dbOperator executeQuery: sql intoResult: & res ] ) {
				NSLog( @"fail to exec sql, message: %@, sql: %@ ", [ dbOperator errorMessage ], sql );
			}
			PQclear( res );
		}
		//	----
		
		[ dbOperator disconnect ];
	}
	return YES;
}

@end
